﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FirmaTransportowa
{
    public partial class DispatcherAddNewTransportForm : Form
    {
        User user = new User();
        float totalLoad = 0.0f;
        float maxLoad = 0.0f;
        int selectedDriverID = 0;
        int selectedVehicleID = 0;
        List<String> packages = new List<String>();
        List<String> sortedPackages = new List<String>();

        DispatcherPanelPresenter presenter;

        public DispatcherAddNewTransportForm(DispatcherPanelPresenter presenter, User user)
        {
            this.user = user;
            this.presenter = presenter;
            InitializeComponent();
        }

        private void DispatcherAddNewTransport_Shown(object sender, EventArgs e)
        {
            packagesList.Columns[0].HeaderText = "ID";
            packagesList.Columns[1].HeaderText = "Status";
            packagesList.Columns[2].HeaderText = "Data dodania";
            packagesList.Columns[3].HeaderText = "Waga";
            packagesList.Columns[4].HeaderText = "Miejscowość startowa";
            packagesList.Columns[5].HeaderText = "Miejscowość docelowa";
            packagesList.Columns[0].Width = 100;
            packagesList.Columns[1].Width = 70;
            packagesList.Columns[2].Width = 65;
            packagesList.Columns[3].Width = 40;
            packagesList.Columns[4].Width = 135;
            packagesList.Columns[5].Width = 135;
        }

        private void displayPackages(String query)
        {
            SqlConnection dbConnection = SqlConnectionFactory.getConnection();
            dbConnection.Open();
            SqlCommand command = new SqlCommand(query, dbConnection);

            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            DataTable dataTable = new DataTable();
            dataAdapter.Fill(dataTable);

            BindingSource bindingSource = new BindingSource();
            bindingSource.DataSource = dataTable;
            packagesList.DataSource = bindingSource;
            /*foreach(DataGridViewColumn column in packagesList.Columns)
            {
                column.SortMode = DataGridViewColumnSortMode.NotSortable;
            }*/
            dbConnection.Close();
        }




        private void driverList_MouseClick(object sender, MouseEventArgs e)
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();

            try
            {
                connection.Open();

                SqlCommand selectDrivers = new SqlCommand("SELECT w.name, w.surname, w.worker_ID " +
                    "FROM Worker w WHERE w.worker_ID NOT IN (" +
                    "SELECT wo.worker_ID FROM Worker wo JOIN Transport t ON wo.worker_ID = t.driver_ID " +
                    "WHERE t.transport_date = '" + datePicker.Value + "' " +
                    "AND ((t.transport_start_time BETWEEN '" + startTimePicker.Value + "' AND '" + endTimePicker.Value + "') " +
                    "OR (t.transport_end_time BETWEEN '" + startTimePicker.Value + "' AND '" + endTimePicker.Value + "') " +
                    "OR (t.transport_start_time < '" + startTimePicker.Value + "' AND t.transport_end_time > '" + endTimePicker.Value + "'))) " +
                    "AND w.occupation_ID = (SELECT occupation_ID FROM Occupation WHERE name = 'kierowca')", connection);
                SqlDataReader driversReader = null;
                driversReader = selectDrivers.ExecuteReader();

                driverList.Items.Clear();
                while (driversReader.Read())
                {
                    driverList.Items.Add(driversReader["name"].ToString() + " " + driversReader["surname"].ToString() + " " + driversReader["worker_ID"].ToString());
                }
                driversReader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                connection.Close();
            }
        }

        private void vehicleList_MouseClick(object sender, MouseEventArgs e)
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();

            connection.Open();

            if(driverList.SelectedIndex != -1)
            {
                SqlCommand selectVehicles = new SqlCommand("SELECT v.brand, v.model, v.capacity, v.vehicle_ID " +
                    "FROM Vehicle v WHERE v.vehicle_ID NOT IN (" +
                    "SELECT ve.vehicle_ID FROM Vehicle ve JOIN Transport t ON ve.vehicle_ID = t.vehicle_ID " +
                    "WHERE transport_date = '" + datePicker.Value + "' " +
                    "AND (transport_start_time BETWEEN '" + startTimePicker.Value + "' AND '" + endTimePicker.Value + "') " +
                    "OR (transport_end_time BETWEEN '" + startTimePicker.Value + "' AND '" + endTimePicker.Value + "') " +
                    "OR (transport_start_time < '" + startTimePicker.Value + "' AND transport_end_time > '" + endTimePicker.Value + "')) " +
                    "AND v.licence_ID IN (SELECT licence_ID FROM Worker_Licence WHERE driver_ID = '" + selectedDriverID + "')", connection);
                SqlDataReader vehiclesReader = null;
                vehiclesReader = selectVehicles.ExecuteReader();
                vehiclesList.Items.Clear();
                while (vehiclesReader.Read())
                {
                    vehiclesList.Items.Add(vehiclesReader["brand"].ToString() + " " + vehiclesReader["model"].ToString() + " " + vehiclesReader["capacity"].ToString() + " " + vehiclesReader["vehicle_ID"].ToString());
                }
                vehiclesReader.Close();
            } else {
                MessageBox.Show("Wybierz kierowcę");
            }

            connection.Close();
        }

        private void updateLoad() {
            progressBar1.Maximum = (int)maxLoad;
            progressBar1.Value = (int)totalLoad;
            label9.Text = totalLoad.ToString() + "/" + maxLoad.ToString() + " kg";
        }
            
        private void button4_Click(object sender, EventArgs e)
        {
            String errorMessage = String.Empty;
             
            if (cityInput.Text == "")
                if(radioButton1.Checked) errorMessage += "Podaj miejscowość startową!\n";
                else errorMessage += "Podaj miejscowość docelową!\n";
            if (driverList.SelectedIndex == -1) errorMessage += "Wybierz kierowcę!\n";
            if (vehiclesList.SelectedIndex == -1) errorMessage += "Wybierz pojazd!\n";

            if (errorMessage == String.Empty)
            {
                float selectedWeight = 0;
                foreach (DataGridViewRow row in packagesList.SelectedRows)
                    if (!packages.Contains(row.Cells[0].Value.ToString()))
                        selectedWeight += float.Parse(row.Cells[3].Value.ToString());

                if (selectedWeight + totalLoad <= maxLoad)
                    foreach (DataGridViewRow row in packagesList.SelectedRows)
                    {
                        if (!packages.Contains(row.Cells[0].Value.ToString()))
                        {
                            addedPackagesList.Items.Add("numer: " + row.Cells[0].Value.ToString() + "   waga: " + row.Cells[3].Value.ToString());
                            totalLoad += float.Parse(row.Cells[3].Value.ToString());
                            packages.Add(row.Cells[0].Value.ToString());
                            updateLoad();
                            packagesList.CurrentCell = null;
                            row.Visible = false;
                        }
                    }
                else MessageBox.Show("Przekroczono dopuszczalną ładowność pojazdu");
            }
            else MessageBox.Show(errorMessage, "Nie można dodać paczek", MessageBoxButtons.OK);
        }

        private void updatePackagesGridView()
        {
            string additionalQuery = "";
            if (radioButton1.Checked)
            {
                additionalQuery += " WHERE p.status = '" + EnumUtils.getEnumDescription(PackageStatus.NEW) + "'";
                if(cityInput.Text != String.Empty) additionalQuery += " AND CHARINDEX('" + cityInput.Text + "', s.city) <> 0";
            }
            else
            {
                additionalQuery += " WHERE p.status = '" + EnumUtils.getEnumDescription(PackageStatus.SORTED) + "'";
                if(cityInput.Text != String.Empty) additionalQuery += " AND CHARINDEX('" + cityInput.Text + "', r.city) <> 0";
            }
            displayPackages("SELECT p.package_ID, p.status, p.add_date, p.weight, s.city, r.city FROM (Package as p JOIN Sender as s ON s.sender_ID = p.sender_ID) JOIN Recipient as r ON r.recipient_ID = p.recipient_ID" + additionalQuery);

            foreach(DataGridViewRow row in packagesList.Rows)
            {
                if(packages.Contains(row.Cells[0].Value.ToString()))
                {
                    row.Visible = false;
                }
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            updatePackagesGridView();
        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            if (radioButton1.Checked)
            {
                if (radioButton1.Checked) label2.Text = "Miejscowość startowa";
                updatePackagesGridView();
                addedPackagesList.Items.Clear();
                packages.Clear();
                totalLoad = 0.0f;
                updateLoad();
            }
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            if (radioButton2.Checked)
            {
                if (radioButton2.Checked) label2.Text = "Miejscowość docelowa";
                updatePackagesGridView();
                addedPackagesList.Items.Clear();
                packages.Clear();
                totalLoad = 0.0f;
                updateLoad();
            }
        }

        private void driverList_SelectedIndexChanged(object sender, EventArgs e)
        {
            selectedDriverID = int.Parse(driverList.SelectedItem.ToString().Split(' ').Last());
        }

        private void addTransportButton_Click(object sender, EventArgs e)
        {
            String errorMessage = String.Empty;
            if(cityInput.Text == String.Empty)
            {
                if (radioButton1.Checked) errorMessage += "Podaj miejscowość startową!\n";
                else errorMessage += "Podaj miejscowość docelową!\n";
            }
            if(distanceInput.Text == String.Empty) errorMessage += "Podaj odległość!\n";
            if(driverList.SelectedIndex == -1) errorMessage += "Wybierz kierowcę!\n";
            if(vehiclesList.SelectedIndex == -1) errorMessage += "Wybierz pojazd!\n";
            if(addedPackagesList.Items.Count == 0) errorMessage += "Przydziel paczki do transportu!\n";
            if(startTimePicker.Value >= endTimePicker.Value) errorMessage += "Czas zakończenia musi być większy od czasu rozpoczęcia!\n";

            if (errorMessage == String.Empty)
            {
                //dodanie nowego transportu
                string startPlace = "";
                string destinationPlace = "";
                string newStatus = "";
                if (radioButton1.Checked)
                {
                    startPlace = cityInput.Text;
                    destinationPlace = "sortownia";
                    newStatus = EnumUtils.getEnumDescription(PackageStatus.RECEIVED);
                } else {
                    startPlace = "sortownia";
                    destinationPlace = cityInput.Text;
                    newStatus = EnumUtils.getEnumDescription(PackageStatus.DELIVERED);
                }

                SqlConnection connection = SqlConnectionFactory.getConnection();
                connection.Open();
                int createdId = 0;

                try
                {
                    SqlCommand addTransportCommand = new SqlCommand("INSERT INTO TRANSPORT (vehicle_ID, worker_ID, driver_ID, status, transport_date, " +
                                                     "transport_start_time, transport_end_time, start_city, destination_city, distance) " +
                                                     "VALUES ('" + selectedVehicleID + "', '" + user.id + "', '" + selectedDriverID + "', " +
                                                     "'" + EnumUtils.getEnumDescription(TransportStatus.PLANNED) + "', '" + datePicker.Value + "', '" + startTimePicker.Value + "', '" + endTimePicker.Value +
                                                     "', '" + startPlace + "', '" + destinationPlace + "', '" + distanceInput.Text + "') SELECT CONVERT(int, SCOPE_IDENTITY())", connection);
                    //addTransportCommand.ExecuteNonQuery();
                    createdId = (int)addTransportCommand.ExecuteScalar();
                }
                catch (Exception ex) {
                    System.Console.Write(ex.Message + "\n" + ex.StackTrace);
                }
                string packagesIdSet = "";
                packages.ForEach(delegate(String package) { 
                       packagesIdSet += ", " + package;
                });
                packagesIdSet = packagesIdSet.Substring(2);

                if (startPlace == "sortownia")
                {
                    SqlCommand updatePackagesCommand = new SqlCommand("UPDATE Package SET second_transport_ID = '" + createdId + "' WHERE package_ID IN (" + packagesIdSet + ")", connection);
                    updatePackagesCommand.ExecuteNonQuery();
                }
                else
                {
                    SqlCommand updatePackagesCommand = new SqlCommand("UPDATE Package SET first_transport_ID = '" + createdId + "' WHERE package_ID IN (" + packagesIdSet + ")", connection);
                    updatePackagesCommand.ExecuteNonQuery();
                }


                try
                {
                    connection.Close();
                }
                catch(Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }

                DialogResult result = MessageBox.Show("Nowy transport dodano poprawnie", "Dodano transport", MessageBoxButtons.OK);
                if (result == DialogResult.OK) this.Hide();
            }
            else 
            {
                MessageBox.Show(errorMessage, "Nie można utworzyć transportu", MessageBoxButtons.OK);
            }
        }

        private void vehiclesList_SelectedIndexChanged(object sender, EventArgs e)
        {
            maxLoad = float.Parse(vehiclesList.Items[vehiclesList.SelectedIndex].ToString().Split(' ').ElementAt(2));
            updateLoad();
            selectedVehicleID = int.Parse(vehiclesList.Items[vehiclesList.SelectedIndex].ToString().Split(' ').Last());
        }

        private void DispatcherAddNewTransportForm_HelpButtonClicked(object sender, CancelEventArgs e)
        {
            HelpUtils.showHelp(this, 10);
        }

        protected override bool ProcessCmdKey(ref Message msg, Keys keyData)
        {
            if (keyData == Keys.F1)
            {
                HelpUtils.showHelp(this, 10);
                return true;
            }
            return base.ProcessCmdKey(ref msg, keyData);
        }
    }
}